package admission.ecnu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import admission.ecnu.db.DBConnection;
import admission.ecnu.object.OClassInfo;
import admission.ecnu.po.User;

public class classInfoDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	public classInfoDao() {
		// TODO Auto-generated constructor stub
		this.conn = DBConnection.getConnection();
	}
	
	public OClassInfo classSelectById(String course_id){
		OClassInfo objClass = new OClassInfo();
		try {
			this.pstmt = this.conn
					.prepareStatement("select course_id,course_name,teacher,course_type,grade,time,place,stu_num, is_test from course_info where course_id=?");
			this.pstmt.setString(1, course_id);
			this.rs = this.pstmt.executeQuery();
			
			objClass.setGrade(rs.getString("course_id"));
			objClass.setClassroom(rs.getString("course_name"));
			objClass.setCurriculum(rs.getString("teacher"));
			objClass.setIsExam(rs.getString("course_type"));
			objClass.setStudentNum(rs.getString("grade"));
			objClass.setTeacher(rs.getString("time"));
			objClass.setClassTime(rs.getString("place"));
			objClass.setType(rs.getString("stu_num"));
			objClass.setIsExam(rs.getString("is_test"));

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				this.rs.close();
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
			
		}
		return objClass;
	}
	
	public List<OClassInfo> classSelect(){
		List<OClassInfo> list = new ArrayList<OClassInfo>();
		try {
			this.pstmt = this.conn.prepareStatement("select course_id,course_name,teacher,course_type,grade,time,place,stu_num, is_test from course_info");
			this.rs = this.pstmt.executeQuery();
			while(this.rs.next()){
				OClassInfo objClass = new OClassInfo();
				objClass.setGrade(rs.getString("course_id"));
				objClass.setClassroom(rs.getString("course_name"));
				objClass.setCurriculum(rs.getString("teacher"));
				objClass.setIsExam(rs.getString("course_type"));
				objClass.setStudentNum(rs.getString("grade"));
				objClass.setTeacher(rs.getString("time"));
				objClass.setClassTime(rs.getString("place"));
				objClass.setType(rs.getString("stu_num"));
				objClass.setIsExam(rs.getString("is_test"));
				list.add(objClass);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				this.rs.close();
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return list;
	}
	
	public int classInsert(String username,String userpass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("insert into t_user(username,userpass) value(?,?)");
			this.pstmt.setString(1,username);
			this.pstmt.setString(2,userpass);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public int classInsert(OClassInfo newClass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("insert into course_info(course_id,course_name,teacher,course_type,grade,time,place,stu_num,is_test) values(?,?,?,?,?,?,?,?,?)");
			this.pstmt.setString(1, newClass.getClassId());
			this.pstmt.setString(2, newClass.getCurriculum());
			this.pstmt.setString(3, newClass.getTeacher());
			this.pstmt.setString(4, newClass.getType());
			this.pstmt.setString(5, newClass.getGrade());
			this.pstmt.setString(6, newClass.getClassTime());
			this.pstmt.setString(7, newClass.getClassroom());
			this.pstmt.setString(8, newClass.getStudentNum());
			this.pstmt.setString(9, newClass.getIsExam());
			
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public void classInsert(List classList){
		
		for(int j = 0; j < classList.size(); j++){
			classInsert((OClassInfo)classList.get(j));
		}
	}
	
	public int classDelete(String id){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("delete from course_info where id in(?)");
			this.pstmt.setString(1, id);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public int classUpdate(String id,String username,String userpass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("update t_user set username=?,userpass=? where id=?");
			this.pstmt.setString(1,	username);
			this.pstmt.setString(2, userpass);
			this.pstmt.setString(3, id);
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	public int classUpdate(OClassInfo newClass){
		int i = 0;
		try {
			this.pstmt = this.conn.prepareStatement("update course_info set course_name=?,teacher=?,course_type=?,grade=?,time=?,place=?,stu_num=?,is_test=? where course_id=?");
			this.pstmt.setString(1, newClass.getCurriculum());
			this.pstmt.setString(2, newClass.getTeacher());
			this.pstmt.setString(3, newClass.getType());
			this.pstmt.setString(4, newClass.getGrade());
			this.pstmt.setString(5, newClass.getClassTime());
			this.pstmt.setString(6, newClass.getClassroom());
			this.pstmt.setString(7, newClass.getStudentNum());
			this.pstmt.setString(8, newClass.getIsExam());
			this.pstmt.setString(9, newClass.getClassId());
			i = this.pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return i;
	}
	
	public void classUpdate(List<OClassInfo> classList){
		for(int i = 0; i<classList.size(); i++){
			classUpdate(classList.get(i));
		}
	}
	
	public List<User> classUpdateLoad(String id){
		List<User> list = new ArrayList<User>();
		try {
			this.pstmt = this.conn.prepareStatement("select username,userpass from t_user where id = ?");
			this.pstmt.setString(1, id);
			this.rs = this.pstmt.executeQuery();
			while(this.rs.next()){
				User user = new User();
				user.setUsername(rs.getString("username"));
				user.setUserpass(rs.getString("userpass"));
				list.add(user);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				this.pstmt.close();
				this.rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.closeConnection(this.conn);
		}
		return list;
	}
}